if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Populate21SecurityData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Populate21SecurityData]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/******************************************************************
* Implements security for all Study Staff for protocols
*
* PARAMETERS
* Name					Description					
* -------------			-------------------------------------------
*					
******************************************************************/
CREATE PROCEDURE [dbo].[Populate21SecurityData]
AS
BEGIN
	SET NOCOUNT ON

	-- declare locals
	DECLARE	@orgAccess			VARCHAR(4)
	DECLARE	@portfolioAccess	VARCHAR(4)
	DECLARE	@directAccess		VARCHAR(4)
	DECLARE @protocolResource	INT
	DECLARE @agreementResource	INT
	DECLARE @userName			VARCHAR(12)
	DECLARE @userID				INT
	
	SET @orgAccess			= 'ORG'
	SET @portfolioAccess	= 'PORT'
	SET @directAccess		= 'DIR'
	SET @protocolResource	= 2
	SET @agreementResource	= 1

	-- Cleanup after last run
	truncate table tmp21SecurityData


	DECLARE userNameCursor CURSOR FAST_FORWARD FOR
		select UserName from [dbo].[UserIdsForVerify]

	OPEN userNameCursor
	FETCH NEXT FROM userNameCursor INTO @userName

	WHILE @@FETCH_STATUS = 0
		BEGIN

			SET @userID = (select Id from FwkDomainUser where UserName = @userName)
	
			-- PROTOCOLS!
			-- ORGS
			insert into tmp21SecurityData (FolderNumber, UserName, AccessType, Organization) 
			select distinct(P.ProtocolNumber) as FolderNumber, @userName as UserName, @orgAccess as AccessType, O.FullyQualifiedName
			from Protocol P
				INNER JOIN dbo.fnAdmQGetOrganizationAuthorizedResourceList (@userID, @protocolResource) orgRes
					on orgRes.resourceid = P.fwkresourceid 
				INNER JOIN FwkDomainOrganization O
					on O.Id = P.PIFwkDomainOrganizationId
			where P.IsCurrent = 1 

			-- DIRECT
			insert into tmp21SecurityData (FolderNumber, UserName, AccessType) 
			select distinct(P.ProtocolNumber) as FolderNumber, @userName as UserName, @directAccess as AccessType
			from Protocol P
				INNER JOIN dbo.fnFwkQGetDirectAccessAuthorizedResources (@userID, @protocolResource) orgRes
					on orgRes.resourceid = P.fwkresourceid 
			where P.IsCurrent = 1 

			-- Portfolio
			insert into tmp21SecurityData (FolderNumber, UserName, AccessType) 
			select distinct(P.ProtocolNumber) as FolderNumber, @userName as UserName, @portfolioAccess as AccessType
			from Protocol P
				INNER JOIN dbo.fnAdmQGetPortfolioAuthorizedResourceList (@userID, @protocolResource) orgRes
					on orgRes.resourceid = P.fwkresourceid 
			where P.IsCurrent = 1 


			-- AGREEMENTS
			-- ORGS
			insert into tmp21SecurityData (FolderNumber, UserName, AccessType, Organization) 
			select distinct(A.FolderNumber) as FolderNumber, @userName as UserName, @orgAccess as AccessType, O.FullyQualifiedName
			from AgrAgreement A
				INNER JOIN dbo.fnAdmQGetOrganizationAuthorizedResourceList (@userID, @agreementResource) orgRes
					on orgRes.resourceid = A.fwkresourceid 
				INNER JOIN SvcEntity Svc
					on A.SvcEntityId = Svc.Id
				inner join SvcEntity_FwkDomainOrganization_Map svcMap
					on svcMap.SvcEntityId = Svc.Id
				INNER JOIN FwkDomainOrganization O
					on O.Id = svcMap.FwkDomainOrganizationId
			where A.IsCurrent = 1 
				and svcMap.FwkOrganizationRelationshipTypeId = 1

			-- DIRECT
			insert into tmp21SecurityData (FolderNumber, UserName, AccessType) 
			select distinct(a.FolderNumber) as FolderNumber, @userName as UserName, @directAccess as AccessType
			from AgrAgreement A
				INNER JOIN dbo.fnFwkQGetDirectAccessAuthorizedResources (@userID, @agreementResource) orgRes
					on orgRes.resourceid = a.fwkresourceid 
			where A.IsCurrent = 1 

			-- Portfolio
			insert into tmp21SecurityData (FolderNumber, UserName, AccessType) 
			select distinct(a.FolderNumber) as FolderNumber, @userName as UserName, @portfolioAccess as AccessType
			from AgrAgreement A
				INNER JOIN dbo.fnAdmQGetPortfolioAuthorizedResourceList (@userID, @agreementResource) orgRes
					on orgRes.resourceid = A.fwkresourceid 
			where A.IsCurrent = 1 

			

			FETCH NEXT FROM userNameCursor INTO @userName

		END

	CLOSE userNameCursor
	DEALLOCATE userNameCursor


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

